﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtility;

namespace SQLServerDAL.LED
{
    public class FinanceRecordBase
    { 
        private SqlParameter[] Parms;
        private SqlConnection Conn;

        public FinanceRecordBase()
        {
            Conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
        }

        #region 信息添加
        /// <summary>
        /// 信息添加
        /// </summary>
        /// <param name="_obj"></param>
        /// <returns></returns>
        public string CreateFinanceRecord(Model.LED.FinanceRecordBase _obj)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@Category", SqlDbType.VarChar,256),
					new SqlParameter("@Subcategory", SqlDbType.VarChar,256),
					new SqlParameter("@CategoryCode", SqlDbType.VarChar,256),
					new SqlParameter("@SubcategoryCode", SqlDbType.VarChar,256),
					new SqlParameter("@HandledBy", SqlDbType.VarChar,256),
					new SqlParameter("@TypeCode", SqlDbType.Int,4),
					new SqlParameter("@EventDate", SqlDbType.DateTime),
					new SqlParameter("@Amount", SqlDbType.Decimal,9),
					new SqlParameter("@InvolveObject", SqlDbType.VarChar,512),
					new SqlParameter("@Remark", SqlDbType.VarChar,1024),
					new SqlParameter("@StatusCode", SqlDbType.SmallInt,2),
					new SqlParameter("@CreatedOn", SqlDbType.DateTime),
					new SqlParameter("@CreatedBy", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@ModifyOn", SqlDbType.DateTime),
					new SqlParameter("@ModifyBy", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@InvolveObjectId", SqlDbType.VarChar,256),
                    new SqlParameter("@FinanceRecordId",SqlDbType.UniqueIdentifier,16,"Output"),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = _obj.Category;
            Parms[1].Value = _obj.Subcategory;
            Parms[2].Value = _obj.CategoryCode;
            Parms[3].Value = _obj.SubcategoryCode;
            Parms[4].Value = _obj.HandledBy;
            Parms[5].Value = _obj.TypeCode;
            Parms[6].Value = _obj.EventDate;
            Parms[7].Value = _obj.Amount;
            Parms[8].Value = _obj.InvolveObject;
            Parms[9].Value = _obj.Remark;
            Parms[10].Value = _obj.StatusCode;
            Parms[11].Value = _obj.CreatedOn;
            Parms[12].Value = new Guid(_obj.CreatedBy);
            Parms[13].Value = _obj.ModifyOn;
            Parms[14].Value = new Guid(_obj.ModifyBy);
            Parms[15].Value = _obj.InvolveObjectId;
            Parms[16].Direction = ParameterDirection.Output;
            Parms[17].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "FinanceRecordBase_Create", Parms);
                status = ((Parms[17].Value != null) ? ((int)Parms[17].Value) : -1); 

                switch (status)
                {
                    case 0:
                        result = "succeeded";
                        if (Common.Utils.IsGuid(Parms[16].Value.ToString()))
                            _obj.FinanceRecordId = Parms[16].Value.ToString();

                        result += "|" + _obj.FinanceRecordId;
                        break;
                    default:
                        result = status.ToString();
                        break;
                } 
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 信息修改
        /// <summary>
        /// 信息修改
        /// </summary>
        /// <param name="_obj"></param>
        /// <returns></returns>
        public string ModifyFinanceRecord(Model.LED.FinanceRecordBase _obj)
        {
            Parms = new SqlParameter[] { 
                    new SqlParameter("@Category", SqlDbType.VarChar,256),
					new SqlParameter("@Subcategory", SqlDbType.VarChar,256),
					new SqlParameter("@CategoryCode", SqlDbType.VarChar,256),
					new SqlParameter("@SubcategoryCode", SqlDbType.VarChar,256),
					new SqlParameter("@HandledBy", SqlDbType.VarChar,256),
					new SqlParameter("@TypeCode", SqlDbType.Int,4),
					new SqlParameter("@EventDate", SqlDbType.DateTime),
					new SqlParameter("@Amount", SqlDbType.Decimal,9),
					new SqlParameter("@InvolveObject", SqlDbType.VarChar,512),
					new SqlParameter("@Remark", SqlDbType.VarChar,1024),
					new SqlParameter("@StatusCode", SqlDbType.SmallInt,2),
					new SqlParameter("@CreatedOn", SqlDbType.DateTime),
					new SqlParameter("@CreatedBy", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@ModifyOn", SqlDbType.DateTime),
					new SqlParameter("@ModifyBy", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@InvolveObjectId", SqlDbType.VarChar,256),
                    new SqlParameter("@FinanceRecordId",SqlDbType.UniqueIdentifier),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = _obj.Category;
            Parms[1].Value = _obj.Subcategory;
            Parms[2].Value = _obj.CategoryCode;
            Parms[3].Value = _obj.SubcategoryCode;
            Parms[4].Value = _obj.HandledBy;
            Parms[5].Value = _obj.TypeCode;
            Parms[6].Value = _obj.EventDate;
            Parms[7].Value = _obj.Amount;
            Parms[8].Value = _obj.InvolveObject;
            Parms[9].Value = _obj.Remark;
            Parms[10].Value = _obj.StatusCode;
            Parms[11].Value = _obj.CreatedOn;
            Parms[12].Value = new Guid(_obj.CreatedBy);
            Parms[13].Value = _obj.ModifyOn;
            Parms[14].Value = new Guid(_obj.ModifyBy);
            Parms[15].Value = _obj.FinanceRecordId;
            Parms[16].Value = new Guid(_obj.FinanceRecordId);
            Parms[17].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "FinanceRecordBase_Update", Parms);
                status = ((Parms[17].Value != null) ? ((int)Parms[17].Value) : -1);
                 
                switch (status)
                {
                    case 0:
                        result = "succeeded";
                        result += "|" + _obj.FinanceRecordId;
                        break;
                    default:
                        result = status.ToString();
                        break; 
                } 
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 获取信息实体
        /// <summary>
        /// 获取信息实体
        /// </summary> 
        /// <returns></returns>
        public Model.LED.FinanceRecordBase GetObjById(string financerecordid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM FinanceRecordBase WHERE FinanceRecordId = @FinanceRecordId AND DeletionStateCode = 0");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@FinanceRecordId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(financerecordid);

            Model.LED.FinanceRecordBase _obj = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _obj = new Model.LED.FinanceRecordBase();
                    _obj.FinanceRecordId = rd["FinanceRecordId"].ToString();
                    _obj.Category = rd["Category"].ToString();
                    _obj.Subcategory = rd["Subcategory"].ToString();
                    _obj.CategoryCode = rd["CategoryCode"].ToString();
                    _obj.SubcategoryCode = rd["SubcategoryCode"].ToString();
                    _obj.HandledBy = rd["HandledBy"].ToString();
                    _obj.TypeCode = int.Parse(rd["TypeCode"].ToString());
                    _obj.EventDate = DateTime.Parse(rd["EventDate"].ToString());
                    _obj.Amount = decimal.Parse(rd["Amount"].ToString());
                    _obj.InvolveObject = rd["InvolveObject"].ToString();
                    _obj.InvolveObjectId = (string.IsNullOrEmpty(rd["InvolveObjectId"].ToString()) ? "" : rd["InvolveObjectId"].ToString().Trim());
                    _obj.Remark = rd["Remark"].ToString();
                    _obj.StatusCode = int.Parse(rd["StatusCode"].ToString());
                    _obj.CreatedOn = DateTime.Parse(rd["CreatedOn"].ToString());
                    _obj.CreatedBy = rd["CreatedBy"].ToString();
                    _obj.ModifyOn = DateTime.Parse(rd["ModifyOn"].ToString());
                    _obj.ModifyBy = rd["ModifyBy"].ToString();
                }
            }
            catch
            {
                return null;
            }
            return _obj;
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="financerecordid">产品Id</param>
        /// <param name="isLogical">是否逻辑删除(True 逻辑删除，False 物理删除)</param>
        public string Delete(string financerecordid, bool isLogical)
        {
            string result = "";
            SqlParameter[] parameters = new SqlParameter[] {
					new SqlParameter("@FinanceRecordId", SqlDbType.UniqueIdentifier),
					new SqlParameter("@isLogical", SqlDbType.Bit),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                };
            parameters[0].Value = new Guid(financerecordid);
            parameters[1].Value = isLogical;
            parameters[2].Direction = ParameterDirection.ReturnValue;

            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "FinanceRecordBase_Delete", parameters);
                int status = ((parameters[2].Value != null) ? ((int)parameters[2].Value) : -1);
 
                result = status.ToString();
            }
            catch(Exception err)
            {
                result = err.Message;
            }

            return result;
        }
        #endregion
 
        #region 生成报表数据
        /// <summary>
        /// 生成报表数据
        /// </summary> 
        public string BuildingReport(int year, int month, string createdby)
        {
            string result = "";

            SqlParameter[] parameters = new SqlParameter[] {
					new SqlParameter("@CreatedBy", SqlDbType.UniqueIdentifier),
					new SqlParameter("@Year", SqlDbType.Int),
					new SqlParameter("@Month", SqlDbType.Int),
					new SqlParameter("@CreatedOn", SqlDbType.DateTime),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                };
            parameters[0].Value = new Guid(createdby);
            parameters[1].Value = year;
            parameters[2].Value = month;
            parameters[3].Value = DateTime.Now;
            parameters[4].Direction = ParameterDirection.ReturnValue;

            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "FinanceReport_Building", parameters);
                int status = ((parameters[4].Value != null) ? ((int)parameters[4].Value) : -1);

                result = status.ToString();
            }
            catch (Exception err)
            {
                result = err.Message;
            }

            return result;
        }
        #endregion
    }
}
